package thant.sqlgear;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import thant.common.map.CommonMap;
import thant.common.map.SQLMap;
import static thant.sqlgear.SQL.*;

public class App {
	private static void print(String log) {
		StackTraceElement[] stackTraceElements= new Throwable().getStackTrace();
		System.out.print("LINE "+stackTraceElements[1].getLineNumber()+":"+log);
	}
	
	private static void printResult(List<Map<String, Object>> result) {
		String spliter;
		for (int i = 0; i<result.size(); ++i) {
			Map<String, Object> row = result.get(i);
			if (0 == i) {
				spliter = "";
				for (Entry<String, Object> field : row.entrySet()) {
					System.out.print(spliter+field.getKey());
					spliter = "\t";
				}
				System.out.println("\n-------------------------------------------------");
			}
			spliter = "";
			for (Entry<String, Object> field : row.entrySet()) {
				System.out.print(spliter+field.getValue());
				spliter = "\t";
			}
			System.out.println();
		}
	}
	
    public static void main(String[] args)
    {
    	//删除数据库文件，重新开始
    	new File("z:/test.db").delete(); //注意修改这里的路径为本机有效的路径
    	
    	String sql = "jdbc:sqlite://z:/test.db"; //和上面路径一致
		Connection conn = null;
		try{
			Class.forName("org.sqlite.JDBC");

			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(sql);
			
			SQLMap map = new SQLMap();
			SQLRunner dao = new SQLRunner();
			dao.update(conn, "CREATE TABLE t_person (",
				"psn_id VARCHAR(32) NOT NULL PRIMARY KEY,",
				"psn_name VARCHAR(50) NOT NULL,",
				"psn_type VARCHAR(32) NULL,",
				"psn_phone VARCHAR(20) NULL)");
			print(dao.getLastSql()+"\n");
			print("table created.\n");

			SQLScript src = new SQLScript("z:\\test.txt");
			SQLRunner dao1 = new SQLRunner(conn);
			src.runSQL(dao1, "init", new CommonMap());
			print(dao1.getLastSql()+"\n");

			CommonMap sret = (CommonMap)src.runSQL(dao1, "queryByID", new CommonMap(){{ put("id", "12321"); }});
			print(dao1.getLastSql()+"\n");
			printResult((List)sret.get("data"));
			dao1.close();
			
			int rows = map.INSERT("t_person").VALUES(
					"psn_id", "0",
					"psn_name", "xwf",
					"psn_type", "admin",
					"psn_phone", "12399990000"
				).update(dao, conn);
			print(dao.getLastSql()+"\n");
			print(rows+" rows inserted.\n");

			rows = map.INSERT("t_person").VALUESLIST("1", "xwf", "admin", "12399990000").update(dao, conn);
			print(dao.getLastSql()+"\n");
			rows += map.INSERT("t_person").VALUESLIST("2", "xwf", "admin", "12399990000").update(dao, conn);
			print(dao.getLastSql()+"\n");
			rows += map.INSERT("t_person").VALUESLIST("3", "xwf", "admin", "12399990000").update(dao, conn);
			print(dao.getLastSql()+"\n");
			rows += map.INSERT("t_person").VALUESLIST("4", "xwf", "admin", "12399990000").update(dao, conn);
			print(dao.getLastSql()+"\n");
			rows += map.INSERT("t_person").VALUESLIST("5", "xwf", "admin", "12399990000").update(dao, conn);
			print(dao.getLastSql()+"\n");
			print(rows+" rows inserted.\n");
			
			CommonMap argmap = new CommonMap(false,
					"psn_id", "6",
					"psn_name", "boss",
					"psn_type", "boss",
					"psn_phone", "12399990000"
				);
			rows = map.UPDATE("t_person").VALUESMAP(argmap).update(dao, conn);
			print(dao.getLastSql()+"\n");
			print(rows+" rows updated.\n");
			
			rows = map.REPLACE("t_person").VALUES(
					"psn_name", "boss",
					"psn_type", "boss",
					"psn_phone", "12399990000"
				).WHERE("psn_id", "=", "5").update(dao, conn);
			print(dao.getLastSql()+"\n");
			print(rows+" rows replaced.\n");
			
			rows = dao.update(conn, "UPDATE t_person",
				SET(
					L("psn_name=", V("管理员")),
					L("psn_phone=", V("13112341234"))
				),
				"WHERE psn_id=", V("0")
			);
			print(dao.getLastSql()+"\n");
			print(rows+" rows updated.\n");
			
			rows = dao.update(conn, "UPDATE t_person",
				SET(
					"psn_name", "前台",
					"psn_phone", "13912332234"
				),
				"WHERE psn_id=", V("2")
			);
			print(dao.getLastSql()+"\n");
			print(rows+" rows updated.\n");
			
			rows = dao.update(conn, "UPDATE t_person",
				SET(
					"psn_name", "清洁工",
					"psn_phone", "13000341234"
				),
				WHERE(
					"psn_id", "=", "3"
				)
			);
			print(dao.getLastSql()+"\n");
			print(rows+" rows updated.\n");
			
			rows = dao.update(conn, "INSERT INTO t_person(psn_id, psn_name, psn_phone)",
				VALUES(
					V("7"),
					V("猫猫"),
					V("131168744489")
				)
			);
			print(dao.getLastSql()+"\n");
			print(rows+" rows inserted.\n");
			
			rows = dao.update(conn, "INSERT INTO t_person",
				VALUES(
					"psn_id", "8",
					"psn_name", "狗狗",
					"psn_phone", "130976545676"
				)
			);
			print(dao.getLastSql()+"\n");
			print(rows+" rows inserted.\n");
			
			List<Map<String, Object>> ret = map.FROM("t_person").query(dao, conn);
			print(dao.getLastSql()+"\n");
			printResult(ret);
			
			print("Connection closed.\n");
			conn.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try{
				if(conn != null) {
					conn.close();
					conn = null;
				}
			} catch(SQLException se) {
				se.printStackTrace();
			}
		}
    }
}

/*class testTmp<T> {
	@SuppressWarnings("hiding")
	public <T> T get(T t) {
		return t;
	}
	
	public void ok(T t) {
		this.<T>get(t);
	}
	
	this(new String(input, 0, inputLength), features)
}*/
